<?php

namespace app\admin\command;

use Carbon\Carbon;
use fast\Random;
use think\console\Command;
use think\console\Input;
use think\console\input\Option;
use think\console\Output;
use think\Db;
use think\Env;
use think\Exception;
use think\Log;

use app\admin\model\sport\Area as AreaModel;
use app\admin\model\sport\ScoreType as ScoreTypeModel;
use app\admin\model\sport\Score as ScoreModel;
use app\common\library\Score as ScoreLib;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Style\Protection;

use app\common\library\Batch;


class Test extends Command
{
    protected $scoreTypes = [
        'middle'  => '中考体育',
        'senior'  => '高考体育',
    ];

    // 在CalPersonSalary后运行
    protected function configure()
    {
        $this->setName('test')
            ->setDescription('update depart fee  零点并在结算薪资后运行');
    }
    
    protected function execute(Input $input, Output $output)
    {
        @ini_set("memory_limit",'-1');
        $stime=microtime(true);
        /*
        $str = "分.秒.毫秒";
        echo mb_substr_count($str, ".");
        $score = "2.35.";
        echo trim($score, ".");
         */
        $str = "70";
        $tmpMillSec = intval($str) * 10 / 1000;
        echo $tmpMillSec;
        exit;
        /*
        $str = "3.10.12";
        $mins = 0;
        if (str_replace(".", "", $str) != $str) {
            $tmpArr = explode(".", $str);
            $countArr = count($tmpArr);
            // 有毫秒的将毫秒转为秒
            $tmpMin = $tmpArr[0];
            $tmpSec = $tmpArr[1];
            if ($countArr > 2) {
                $tmpMill = $tmpArr[2];
                $tmpSec = $tmpSec + $tmpMill / 1000;
            }
            $mins = round($tmpMin + $tmpSec / 60, 3);
        }
        echo $mins;
        exit;
         */
        /*
        $inputFileName = "/Users/sss020/wwwroot/phpproject/sports-score-mini-backend/public/uploads/template/陕西省_中考体育_导入模版.xlsx";

        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
        $sheet = $spreadsheet->getActiveSheet();


        // Add a drawing to the header
        $objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooterDrawing();
        $objDrawing->setName('watermark');
        $objDrawing->setPath('/Users/sss020/wwwroot/phpproject/sports-score-mini-backend/public/template/mini/watermark.png');
        // $objDrawing->setHeight(36);

        // Add the drawing object to Header part
        $sheet->getHeaderFooter()->addImage($objDrawing,  \PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooter::IMAGE_FOOTER_CENTER);
        $sheet->getHeaderFooter()->setOddFooter('&C&G');

        // Write the data to excel file
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save("/Users/sss020/Downloads/watermark.xlsx");
        exit;
         */

        $batchLib = new Batch();
        /*
        $areaId = 1;
        $type = 'senior';
        $filename="/Users/sss020/Downloads/sports_senior_template.xlsx";
        $batchLib->createTemplate($areaId, $type, $filename);
         */
        /*
        $areaId = 1;
        $type = 'middle';
        $filename="/Users/sss020/Downloads/sports_middle_template.xlsx";
        $batchLib->createTemplate($areaId, $type, $filename);
        */
        // middle
        // 待分析的xlsx
        $sourcefilename="/Users/sss020/Downloads/sports_middle_template.xlsx";
        // 结果的xlsx
        $filename="/Users/sss020/Downloads/sports_middle_res_1222.xlsx";
        $batchLib->calMiddleScore($sourcefilename, $filename);
        exit;
        /*
        // senior
        // 待分析的xlsx
        $sourcefilename="/Users/sss020/Downloads/senior.xlsx";
        // 结果的xlsx
        $filename="/Users/sss020/Downloads/senior_res.xlsx";
        $batchLib->calSeniorScore($sourcefilename, $filename);
         */

        $res = $batchLib->handleExcel($sourcefilename);
        var_dump($res);

        // $this->createTemplate(); 
        // $this->calScore();

        $etime=microtime(true);//获取程序执行结束的时间
        $total=$etime-$stime;   //计算差值
        $output->writeln($total);
        $output->info('update person job success');
    }

    private function createTemplate()
    {
        $areaId = 1;
        $type = 'middle';
        $filename="/Users/sss020/Downloads/sports_template.xlsx";
        $this->createXls($areaId, $type, $filename);
    }

    private function calScore()
    {
        // 导入表格分析
        // 待分析的xlsx
        $sourcefilename="/Users/sss020/Downloads/sports.xlsx";
        // 结果的xlsx
        $filename="/Users/sss020/Downloads/sports_res.xlsx";
        $reader = IOFactory::createReader('Xlsx');
        $spreadsheet = $reader->load($sourcefilename);
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow  = $worksheet->getHighestRow(); // 取得总行数
        $highestColumn = $worksheet->getHighestColumn(); // 取得总列数
        $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); // 取得总列数
        $excelData = [];
        $i = 0;
        for ($row = 3; $row <= $highestRow; $row++) {
            for ($col = 1; $col <= $highestColumnIndex; $col++) { 
                $excelData[$i][] = (string)$worksheet->getCellByColumnAndRow($col, $row)->getValue();
            }
            $i++;
        }
        $header = $worksheet->getCell('A1')->getValue();
        $areaId = $worksheet->getCell('A8130')->getValue();
        $type = $worksheet->getCell('B8130')->getValue();
        // 取出所有体育项记录
        $sportModel = ScoreModel::where('sport_area_id', $areaId)->where('score_type', $type)->select();
        $sports = [];
        foreach($sportModel as $si) {
            $sports[$si->project_name] = $si->score_setting;
        }
        // 查找区域中中考和高考的评级设置以及中考的固定分
        $areaModel = AreaModel::where('id', $areaId)->where('status', 'normal')->find();
        // 判断有无区域的数据
        if (empty($areaModel)) {
            $res['msg'] = '无对应区域的设置';
            return $res;
        }
        $fixedScore = $areaModel->middle_fixed_score;
        $middleLevelSetting = $areaModel->level_str;
        $seniorLevelSetting = $areaModel->high_level_str;

        $scoreLib = new ScoreLib();
        $newExcelData = [];
        // 查找的列的索引
        foreach ($excelData as $rowData) {
            $data = []; 
            $startIndex = 2;
            $grade = $rowData[0];
            $name = $rowData[1];
            $sex = $rowData[$startIndex++];
            $item1 = $rowData[$startIndex++];
            $score1 = $rowData[$startIndex++];
            $item2 = $rowData[$startIndex++];
            $score2 = $rowData[$startIndex++];

            $item3 = $rowData[$startIndex++];
            $score3 = $rowData[$startIndex++];
            $item4 = $rowData[$startIndex++];
            $score4 = $rowData[$startIndex++];

            $point1 = 0;
            $point2 = 0;
            $point3 = 0;
            $point4 = 0;
            $totalPoint = 0;
            if (!empty($sex)
              && !empty($item1) && !empty($score1) 
              && !empty($item2) && !empty($score2)
              && !empty($item3) && !empty($score3)
              && !empty($item4) && !empty($score4)
            ) {
                // 计算成绩
                if (isset($sports[$item1])) {
                    $point1 = $scoreLib->calPoint($sports[$item1], $score1);
                    $totalPoint += $point1;
                }
                if (isset($sports[$item2])) {
                    $point2 = $scoreLib->calPoint($sports[$item2], $score2);
                    $totalPoint += $point2;
                }
                if (isset($sports[$item3])) {
                    $point3 = $scoreLib->calPoint($sports[$item3], $score3);
                    $totalPoint += $point3;
                }
                if (isset($sports[$item4])) {
                    $point4 = $scoreLib->calPoint($sports[$item4], $score4);
                    $totalPoint += $point4;
                }
                $calRes = $scoreLib->calMiddleScore(null, $fixedScore, null, $middleLevelSetting, $totalPoint);
                $data[] = $grade;
                $data[] = $name;
                $data[] = $sex;
                $data[] = $item1;
                $data[] = $score1;
                $data[] = $point1;

                $data[] = $item2;
                $data[] = $score2;
                $data[] = $point2;

                $data[] = $item3;
                $data[] = $score3;
                $data[] = $point3;

                $data[] = $item4;
                $data[] = $score4;
                $data[] = $point4;

                $data[] = $point1 + $point2 + $point3 + $point4;
                $data[] = $calRes['score_total'];
                $data[] = $calRes['level_code'] . '('. $calRes['level_name'] . ')';
                // $data[] = $calRes['level_name'];
                $newExcelData[] = $data;
            }
        }
        $this->createXls($areaId, $type, $filename, $newExcelData);
    }

    private function setCell($objSheet, $cellName, $formulas)
    {
        $formula = implode(",", $formulas);
        $formula = '"'.$formula.'"';
        $validation = $objSheet->getCell($cellName)->getDataValidation();
        $validation->setType(DataValidation::TYPE_LIST);
        $validation->setErrorStyle(DataValidation::STYLE_STOP);
        $validation->setAllowBlank(false);
        $validation->setShowInputMessage(true);
        $validation->setShowErrorMessage(true);
        $validation->setShowDropDown(true);
        $validation->setErrorTitle('错误提示');
        $validation->setError('输入的值不在下拉选项内');
        $validation->setPromptTitle('请选择');
        // $validation->setFormula1('"男,女"');
        $validation->setFormula1($formula);
    }

    private function createXls($areaId, $type, $filename, $excelData = null)
    {
        $areaModel = AreaModel::where('id', $areaId)->find();
        $sheetName = $areaModel->area_name . $this->scoreTypes[$type] ."成绩换算表";
        // 查询体育类别
        $items = [];
        $sportTypes = ScoreTypeModel::where('sport_area_id', $areaId)->where('status', 'normal')->select();
        // 体育项
        $sportItmes = [];
        foreach($sportTypes as $st) {
            $items[] = $st->type_name;
            $items[] = '成绩';
            // 有得分的
            if (!empty($excelData)) {
                $items[] = '得分';
            }
            $sportModel = ScoreModel::where('sport_area_id', $areaId)->where('sport_type_id', $st->id)->where('score_type', $type)->select();
            $sports = [];
            foreach($sportModel as $si) {
                $sports[] = $si->project_name;
            }
            $sportItems[] = $sports;
        }
        // 有成绩时补充的后面的列
        $colNames = [];
        if (!empty($excelData)) {
            $colNames = ['四项合计', '换算成绩', '等级'];
        }
        $rowArray = array_merge(['班级','姓名','性别'], $items, $colNames);
        //实例化一个新的excel文档
        $newExcel = new Spreadsheet();
        //获取当前操作sheet的对象
        $objSheet = $newExcel->getActiveSheet();
        $objSheet->setTitle($sheetName);//设置当前sheet的标题
        // 中考模版
        $rowNum = 1; 
        // 以下是首行样式
        $objSheet->setCellValue('A1', $sheetName);
        // 结束的列
        $colEndName = "K";
        // 有计算结果用
        if (!empty($excelData)) {
            $colEndName = "R";
        }
        // 合并单元格
        $objSheet->mergeCells('A' . $rowNum.':'.$colEndName.''.$rowNum);
        // 行样式
        $objSheet->getStyle('A'.$rowNum.':'.'A'.$rowNum)->applyFromArray([
            'font' => [
                'bold' => true,
                'size' => 18,
                'color'=>['argb' => '7030a0']
            ],
        ]);
        // 设置合并列的行高
        $objSheet->getRowDimension($rowNum)->setRowHeight(40);
        $objSheet->getStyle('A' . $rowNum)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $objSheet->getStyle('A' . $rowNum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        // 第二行
        $rowNum++;
        // 固定首行内容
        $objSheet->fromArray($rowArray, NULL, 'A' . $rowNum);
        // 设置行高
        $objSheet->getRowDimension($rowNum)->setRowHeight(26);
        $objSheet->getColumnDimension('A')->setWidth(12);
        $objSheet->getColumnDimension('D')->setWidth(12);
        // 没成绩时
        if (empty($excelData)) {
            $objSheet->getColumnDimension('F')->setWidth(12);
            $objSheet->getColumnDimension('H')->setWidth(12);
            $objSheet->getColumnDimension('J')->setWidth(12);
        }  else {
            $objSheet->getColumnDimension('G')->setWidth(12);
            $objSheet->getColumnDimension('J')->setWidth(12);
            $objSheet->getColumnDimension('M')->setWidth(12);
            
        }
        $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('7030a0');
        $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)->applyFromArray([
            'font' => [
                'bold' => true,
                'size' => 14,
                'color'=>['argb' => 'ffffff']
            ],
        ]);
        $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $objSheet->freezePane('A' . ($rowNum + 1));

        // 开始后面的行
        $rowNum++;
        // 当没有计算结果时
        if (empty($excelData)) { 
            // 生成下拉
            for($row=$rowNum; $row< 1000; $row++) {
                // 生成黑线
                $objSheet->getStyle('A' . $row . ':' . $colEndName . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
                $this->setCell($objSheet, 'C' . $row, ["男", "女"]);
                $this->setCell($objSheet, 'D' . $row, $sportItems[0]);
                $this->setCell($objSheet, 'F' . $row, $sportItems[1]);
                $this->setCell($objSheet, 'H' . $row, $sportItems[2]);
                $this->setCell($objSheet, 'J' . $row, $sportItems[3]);
            }
            // 保存区域和考试类别
            $objSheet->setCellValue('A8130', $areaId);
            $objSheet->setCellValue('B8130', $type);
        } else {
            $font = [
                'font' => [
                    'bold' => true,
                    'size' => 11,
                    'color'=>['argb' => 'ff0000']
                 ],
            ];
            $rowCount = count($excelData) + $rowNum;
            // 有计算结果时
            $objSheet->fromArray($excelData, NULL, 'A' . $rowNum);
            for ($row = $rowNum; $row < $rowCount; $row++) {
                // 生成黑线
                $objSheet->getStyle('A' . $row . ':' . $colEndName . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
                $objSheet->getStyle('F'.$row)->applyFromArray($font);
                $objSheet->getStyle('I'.$row)->applyFromArray($font);
                $objSheet->getStyle('L'.$row)->applyFromArray($font);
                $objSheet->getStyle('O'.$row)->applyFromArray($font);
                $objSheet->getStyle('P'.$row)->applyFromArray($font);
                $objSheet->getStyle('Q'.$row)->applyFromArray($font);
                $objSheet->getStyle('R'.$row)->applyFromArray($font);
            }
        }

        // 设置整体列宽
        $objSheet->getDefaultColumnDimension()->setWidth(12);
        // 设置整体居中
        $objSheet->getParent()->getDefaultStyle()->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $writer = new Xlsx($newExcel);
        $writer->save($filename);
    
    }
    
    
    /**
    * xml转为数组
    *
    * @param mixed $xml xml object
    * @param array $options
    * @access public
    * @return array
    */
    public function xmlToArray($xml, $options = []) {
        $defaults = [
            'namespaceRecursive' => false,  //setting to true will get xml doc namespaces recursively
            'removeNamespace' => false,     //set to true if you want to remove the namespace from resulting keys (recommend setting namespaceSeparator = '' when this is set to true)
            'namespaceSeparator' => ':',    //you may want this to be something other than a colon
            'attributePrefix' => '@',       //to distinguish between attributes and nodes with the same name
            'alwaysArray' => array(),       //array of xml tag names which should always become arrays
            'autoArray' => true,            //only create arrays for tags which appear more than once
            'textContent' => '$',           //key used for the text content of elements
            'autoText' => true,             //skip textContent key if node has no attributes or child nodes
            'keySearch' => false,           //optional search and replace on tag and attribute names
            'keyReplace' => false           //replace values for above search values (as passed to str_replace())
        ];
        $options = array_merge($defaults, $options);
        $namespaces = $xml->getDocNamespaces($options['namespaceRecursive']);
        $namespaces[''] = null; //add base (empty) namespace
     
        //get attributes from all namespaces
        $attributesArray = array();
        foreach ($namespaces as $prefix => $namespace) {
            if ($options['removeNamespace']) $prefix = "";
            foreach ($xml->attributes($namespace) as $attributeName => $attribute) {
                //replace characters in attribute name
                if ($options['keySearch']) $attributeName =
                        str_replace($options['keySearch'], $options['keyReplace'], $attributeName);
                $attributeKey = $options['attributePrefix']
                        . ($prefix ? $prefix . $options['namespaceSeparator'] : '')
                        . $attributeName;
                $attributesArray[$attributeKey] = (string)$attribute;
            }
        }
     
        //get child nodes from all namespaces
        $tagsArray = array();
        foreach ($namespaces as $prefix => $namespace) {
            if ($options['removeNamespace']) $prefix = "";
            foreach ($xml->children($namespace) as $childXml) {
                //recurse into child nodes
                $childArray = self::xmlToArray($childXml, $options);
                // list($childTagName, $childProperties) = each($childArray);
                $childTagName = key($childArray);
                $childProperties = current($childArray);
     
                //replace characters in tag name
                if ($options['keySearch']) $childTagName =
                        str_replace($options['keySearch'], $options['keyReplace'], $childTagName);
                //add namespace prefix, if any
                if ($prefix) $childTagName = $prefix . $options['namespaceSeparator'] . $childTagName;
     
                if (!isset($tagsArray[$childTagName])) {
                    //only entry with this key
                    //test if tags of this type should always be arrays, no matter the element count
                    $tagsArray[$childTagName] =
                            in_array($childTagName, $options['alwaysArray']) || !$options['autoArray']
                            ? array($childProperties) : $childProperties;
                } elseif (
                    is_array($tagsArray[$childTagName]) && array_keys($tagsArray[$childTagName])
                    === range(0, count($tagsArray[$childTagName]) - 1)
                ) {
                    //key already exists and is integer indexed array
                    $tagsArray[$childTagName][] = $childProperties;
                } else {
                    //key exists so convert to integer indexed array with previous value in position 0
                    $tagsArray[$childTagName] = array($tagsArray[$childTagName], $childProperties);
                }
            }
        }
     
        //get text content of node
        $textContentArray = array();
        $plainText = trim((string)$xml);
        if ($plainText !== '') $textContentArray[$options['textContent']] = $plainText;
     
        //stick it all together
        $propertiesArray = !$options['autoText'] || $attributesArray || $tagsArray || ($plainText === '')
                ? array_merge($attributesArray, $tagsArray, $textContentArray) : $plainText;
     
        //return node as array
        return [
            $xml->getName() => $propertiesArray
        ];
    }

}
